package cn.btks.resultintegration.achievements.service.impl;

import cn.btks.commonserver.pojo.PageList;
import cn.btks.commonserver.result.Constants;
import cn.btks.commonserver.result.ResultState;
import cn.btks.commonserver.result.StateInfo;
import cn.btks.commonserver.util.JwtUtil;
import cn.btks.resultintegration.achievements.pojo.EnvironmentAttribute;
import cn.btks.resultintegration.achievements.repository.EnvironmentAttributeRepository;
import cn.btks.resultintegration.achievements.service.AchievementsService;
import cn.btks.resultintegration.achievements.service.EnvironmentAttributeService;
import cn.btks.resultintegration.catalog.pojo.Catalog;
import cn.btks.resultintegration.catalog.service.CatalogService;
import cn.btks.resultintegration.feign.FeignClient.BackManageClient;
import cn.btks.resultintegration.feign.FeignObject.City;
import cn.btks.resultintegration.feign.FeignObject.County;
import cn.btks.resultintegration.feign.FeignObject.Unit;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import io.jsonwebtoken.Claims;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.http.HttpServletRequest;
import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author lijun
 * @Date 2021/1/27 11:27
 */
@Service
public class EnvironmentAttributeServiceImpl implements EnvironmentAttributeService {

    @Autowired
    EnvironmentAttributeRepository environmentAttributeRepository;

    @Autowired
    AchievementsService achievementsService;

    @Autowired
    CatalogService catalogService;

    @Autowired
    BackManageClient backManageClient;

    @Override
    public EnvironmentAttribute findById(Long id) {
        Optional<EnvironmentAttribute> optional = environmentAttributeRepository.findById(id);
        if (optional.isPresent()) {
            return optional.get();
        }
        return null;
    }

    @Override
    public ResultState findPage(Integer pageNumber, Integer pageSize, String code, String name, String cityName, Long catalogId) {
        PageRequest pageRequest = PageRequest.of(pageNumber - 1, pageSize);
        Specification<EnvironmentAttribute> specification = new Specification<EnvironmentAttribute>() {
            @Override
            public Predicate toPredicate(Root<EnvironmentAttribute> root,
                                         CriteriaQuery<?> criteriaQuery,
                                         CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                if (!StringUtils.isEmpty(code)) {
                    predicates.add(criteriaBuilder.like(root.get("HJDYBM"),
                            "%" + code + "%"));
                }
                if (!StringUtils.isEmpty(name)) {
                    predicates.add(criteriaBuilder.like(root.get("HJDYMC"),
                            "%" + name + "%"));
                }
                if (!StringUtils.isEmpty(cityName)) {
                    predicates.add(criteriaBuilder.equal(root.get("CITY"), cityName));
                }
                if (catalogId != null) {
                    List<Long> idList = catalogService.findAllCatalogIdById(catalogId);
                    if (idList.size() > 0) {
                        List predicateList = new ArrayList();
                        Predicate[] p = new Predicate[idList.size()];
                        for (Long id : idList) {
                            predicateList.add(criteriaBuilder.equal(root.get("catalogId"), id));
                        }
                        predicateList.toArray(p);
                        predicates.add(criteriaBuilder.or(p));
                    }
                }
                Predicate[] predicates1 = new Predicate[predicates.size()];
                return criteriaBuilder.and(predicates.toArray(predicates1));
            }
        };
        Page<EnvironmentAttribute> configPage = environmentAttributeRepository.findAll(specification, pageRequest);
        if (configPage.getTotalElements() > 0) {
            PageList pageList = new PageList();
            pageList.setResult(configPage.getContent());
            pageList.setPageNum(configPage.getTotalPages());
            pageList.setTotalCount(configPage.getTotalElements());
            return new ResultState(StateInfo.FIND_OK.getCode(), StateInfo.FIND_OK.getMsg(), pageList);
        }
        return new ResultState(StateInfo.FIND_NULL.getCode(), StateInfo.FIND_NULL.getMsg());
    }

    @Override
    public ResultState findPage(Integer pageNumber, Integer pageSize, String code, String name, Long catalogId, Long cityId) {
        PageRequest pageRequest = PageRequest.of(pageNumber - 1, pageSize);
        Specification<EnvironmentAttribute> specification = new Specification<EnvironmentAttribute>() {
            @Override
            public Predicate toPredicate(Root<EnvironmentAttribute> root,
                                         CriteriaQuery<?> criteriaQuery,
                                         CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                if (!StringUtils.isEmpty(code)) {
                    predicates.add(criteriaBuilder.like(root.get("HJDYBM"),
                            "%" + code + "%"));
                }
                if (!StringUtils.isEmpty(name)) {
                    predicates.add(criteriaBuilder.like(root.get("HJDYMC"),
                            "%" + name + "%"));
                }
                if (catalogId != null) {
                    List<Long> idList = catalogService.findAllCatalogIdById(catalogId);
                    if (idList.size() > 0) {
                        List predicateList = new ArrayList();
                        Predicate[] p = new Predicate[idList.size()];
                        for (Long id : idList) {
                            predicateList.add(criteriaBuilder.equal(root.get("catalogId"), id));
                        }
                        predicateList.toArray(p);
                        predicates.add(criteriaBuilder.or(p));
                    }
                }
                ResultState resultState = backManageClient.findById(cityId);
                if (resultState.getData() != null) {
                    Object data = resultState.getData();
                    City city1 = JSONArray.parseObject(JSON.toJSONString(data), City.class);
                    if (city1 != null) {
                        predicates.add(criteriaBuilder.equal(root.get("CITY"), city1.getName()));
                    }
                }
                Predicate[] predicates1 = new Predicate[predicates.size()];
                return criteriaBuilder.and(predicates.toArray(predicates1));
            }
        };
        Page<EnvironmentAttribute> configPage = environmentAttributeRepository.findAll(specification, pageRequest);
        if (configPage.getTotalElements() > 0) {
            PageList pageList = new PageList();
            pageList.setResult(configPage.getContent());
            pageList.setPageNum(configPage.getTotalPages());
            pageList.setTotalCount(configPage.getTotalElements());
            return new ResultState(StateInfo.FIND_OK.getCode(), StateInfo.FIND_OK.getMsg(), pageList);
        }
        return new ResultState(StateInfo.FIND_NULL.getCode(), StateInfo.FIND_NULL.getMsg());
    }

    @Override
    public ResultState findPage(Integer pageNumber, Integer pageSize, List<Long> catalogIdList) {
        PageRequest pageRequest = PageRequest.of(pageNumber - 1, pageSize);
        Specification<EnvironmentAttribute> specification = new Specification<EnvironmentAttribute>() {
            @Override
            public Predicate toPredicate(Root<EnvironmentAttribute> root,
                                         CriteriaQuery<?> criteriaQuery,
                                         CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                if (catalogIdList != null && catalogIdList.size() > 0) {
                    List predicateList = new ArrayList();
                    Predicate[] p = new Predicate[catalogIdList.size()];
                    for (Long id : catalogIdList) {
                        predicateList.add(criteriaBuilder.equal(root.get("catalogId"), id));
                    }
                    predicateList.toArray(p);
                    predicates.add(criteriaBuilder.or(p));
                }
                Predicate[] predicates1 = new Predicate[predicates.size()];
                return criteriaBuilder.and(predicates.toArray(predicates1));
            }
        };
        Page<EnvironmentAttribute> configPage = environmentAttributeRepository.findAll(specification, pageRequest);
        if (configPage.getTotalElements() > 0) {
            PageList pageList = new PageList();
            pageList.setResult(configPage.getContent());
            pageList.setPageNum(configPage.getTotalPages());
            pageList.setTotalCount(configPage.getTotalElements());
            return new ResultState(StateInfo.FIND_OK.getCode(), StateInfo.FIND_OK.getMsg(), pageList);
        }
        return new ResultState(StateInfo.FIND_NULL.getCode(), StateInfo.FIND_NULL.getMsg());
    }

    @Override
    public ResultState findAllByCatalogIdList(List<Long> catalogIdList, HttpServletRequest request) throws Exception {
        String token = request.getHeader("token");
        Optional<Claims> optional = JwtUtil.getClaimsFromToken(token, JwtUtil.jwtsSecret);
        Unit unit = new Unit();
        if (optional.isPresent()) {
            Claims claims = optional.get();
            Object obj = claims.get("unit");
            unit = JSONArray.parseObject(JSON.toJSONString(obj), Unit.class);
            if (unit == null) {
                return new ResultState(StateInfo.TOKEN_ERROR.getCode(), StateInfo.TOKEN_ERROR.getMsg());
            }
        }
        List<Map<String, Object>> results = new ArrayList<>();
        if (catalogIdList != null) {
            for (Long id : catalogIdList) {
                Map<String, Object> result = new HashMap<>();
                List<EnvironmentAttribute> environmentAttributeList = new ArrayList<>();
                if (unit != null) {
                    if (unit.getCityCode().equals("65")) {
                        environmentAttributeList = environmentAttributeRepository.findAllByCatalogId(id);
                    } else {
                        //是否含有代管团场
                        List<County> countyList = new ArrayList<>();
                        ResultState resultState = backManageClient.findAllByEscrowId(unit.getCityId());
                        if(resultState.getData() != null){
                            countyList = JSONArray.parseArray(JSON.toJSONString(resultState.getData()), County.class);
                        }
                        environmentAttributeList = environmentAttributeRepository.findAllByCatalogIdAndCITY(id, unit.getCityName());
                        if(countyList.size() > 0){
                            for (County item : countyList) {
                                List<EnvironmentAttribute> environmentAttributeList1 = environmentAttributeRepository.findAllByCatalogIdAndCOUNTY(id, item.getName());
                                if(!CollectionUtils.isEmpty(environmentAttributeList1)){
                                    environmentAttributeList.addAll(environmentAttributeList1);
                                }
                            }
                        }
                    }
                }
                Catalog catalog = catalogService.findById(id);
                if (catalog != null) {
                    result.put(catalog.getCatalogName(), environmentAttributeList);
                    results.add(result);
                }
            }
        }
        return results != null && results.size() > 0 ? new ResultState(StateInfo.FIND_OK.getCode(), StateInfo.FIND_OK.getMsg(), results) :
                new ResultState(StateInfo.FIND_ERROR.getCode(), StateInfo.FIND_ERROR.getMsg());
    }

    @Override
    public ResultState save(EnvironmentAttribute environmentAttribute) {
        EnvironmentAttribute environmentAttribute1 = environmentAttributeRepository.save(environmentAttribute);
        return environmentAttribute1 != null ? new ResultState(StateInfo.SAVE_OK.getCode(), StateInfo.SAVE_OK.getMsg(), environmentAttribute1) :
                new ResultState(StateInfo.SAVE_ERROR.getCode(), StateInfo.SAVE_ERROR.getMsg());
    }

    @Override
    public ResultState saveAll(List<EnvironmentAttribute> environmentAttributes) {
        List<EnvironmentAttribute> environmentAttributeList = environmentAttributeRepository.saveAll(environmentAttributes);
        return environmentAttributeList != null ? new ResultState(StateInfo.SAVE_OK.getCode(), StateInfo.SAVE_OK.getMsg(), environmentAttributeList) :
                new ResultState(StateInfo.SAVE_ERROR.getCode(), StateInfo.SAVE_ERROR.getMsg());
    }

    @Override
    public int deleteByAchievementId(Long achievementId) {
        return environmentAttributeRepository.deleteByAchievementId(achievementId);
    }

    @Override
    public ResultState export(String code, String name, String cityName, Long catalogId, HttpServletRequest request) throws IOException {
        Specification<EnvironmentAttribute> specification = new Specification<EnvironmentAttribute>() {
            @Override
            public Predicate toPredicate(Root<EnvironmentAttribute> root,
                                         CriteriaQuery<?> criteriaQuery,
                                         CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                if (!StringUtils.isEmpty(code)) {
                    predicates.add(criteriaBuilder.like(root.get("HJDYBM"),
                            "%" + code + "%"));
                }
                if (!StringUtils.isEmpty(name)) {
                    predicates.add(criteriaBuilder.like(root.get("HJDYMC"),
                            "%" + name + "%"));
                }
                if (!StringUtils.isEmpty(cityName)) {
                    predicates.add(criteriaBuilder.equal(root.get("CITY"), cityName));
                }
                if (catalogId != null) {
                    List<Long> idList = catalogService.findAllCatalogIdById(catalogId);
                    if (idList.size() > 0) {
                        List predicateList = new ArrayList();
                        Predicate[] p = new Predicate[idList.size()];
                        for (Long id : idList) {
                            predicateList.add(criteriaBuilder.equal(root.get("catalogId"), id));
                        }
                        predicateList.toArray(p);
                        predicates.add(criteriaBuilder.or(p));
                    }
                }
                Predicate[] predicates1 = new Predicate[predicates.size()];
                return criteriaBuilder.and(predicates.toArray(predicates1));
            }
        };
        List<EnvironmentAttribute> environmentAttributeList = environmentAttributeRepository.findAll(specification);
        Catalog catalog = catalogService.findById(catalogId);
        boolean isGKDY = false;
        if (catalog.getCode().equals("4") || catalog.getCode().equals("41") || catalog.getCode().equals("42") || catalog.getCode().equals("43")) {
            isGKDY = true;
        }
        String filePath = export(request, environmentAttributeList, catalog.getCatalogName(), isGKDY);
        System.out.println(filePath);
        return !StringUtils.isEmpty(filePath) ? new ResultState(StateInfo.DOWNLOAD_OK.getCode(), StateInfo.DOWNLOAD_OK.getMsg(), filePath) :
                new ResultState(StateInfo.DOWNLOAD_ERROR.getCode(), StateInfo.DOWNLOAD_ERROR.getMsg());
    }

    /**
     * 导出excel方法
     *
     * @return
     */
    private String export(HttpServletRequest request, List<EnvironmentAttribute> environmentAttributeList, String fileName, boolean isGKDY) throws IOException {
        //导出文件所在文件夹路径
        String srcFilePathDir = request.getSession(true).getServletContext().getRealPath("//") + Constants.ACHIEVEMENTS_EXPORT_FILE;
        //生成文件路径
        String srcFinalPath = request.getSession(true).getServletContext().getRealPath("//") + Constants.ACHIEVEMENTS_EXPORT_FILE + fileName + ".xlsx";
        //返回路径
        String srcFinalPathResult = Constants.ACHIEVEMENTS_EXPORT_FILE + fileName + ".xlsx";
        //模板文件
        String srcTemplateFile = "";

        if (isGKDY) {
            srcTemplateFile = "/res/achievements/export/template/" + "HJDY.xlsx";
        } else {
            srcTemplateFile = "/res/achievements/export/template/" + "HJYS.xlsx";
        }
        System.out.println(srcFinalPath);
        File dir1 = new File(srcFilePathDir);
        if (!dir1.exists()) {
            dir1.mkdirs();
        }
        File file1 = new File(srcFinalPath);
        if (file1.exists()) {
            file1.delete();
        }

        //Excel工具项
        String url = this.getClass().getResource(srcTemplateFile).getPath();
        System.out.println("url:" + url);
        InputStream inputStream = this.getClass().getResourceAsStream(srcTemplateFile);
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workbook.getSheetAt(0);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setBorderBottom(BorderStyle.NONE);
        cellStyle.setBorderTop(BorderStyle.NONE);
        cellStyle.setBorderLeft(BorderStyle.NONE);
        cellStyle.setBorderRight(BorderStyle.NONE);

        int rowStartIndexTwo = 1;//模板数据起始行索引（行列索引均从0开始）

        if (isGKDY) {
            if (environmentAttributeList != null && environmentAttributeList.size() > 0) {
                int serialNum = 1;
                for (EnvironmentAttribute item : environmentAttributeList) {
                    int columnIndex = 0;
                    Row row = sheet.createRow(rowStartIndexTwo);
                    row.setHeightInPoints(24);
                    //序号
                    CreateCell(cellStyle, row, columnIndex++, String.valueOf(serialNum), sheet);
                    //环境管控单元编码
                    CreateCell(cellStyle, row, columnIndex++, item.getHJDYBM(), sheet);
                    //环境管控单元名称
                    CreateCell(cellStyle, row, columnIndex++, item.getHJDYMC(), sheet);
                    //省级行政单元
                    CreateCell(cellStyle, row, columnIndex++, item.getPROV(), sheet);
                    //市级行政单元
                    CreateCell(cellStyle, row, columnIndex++, item.getCITY(), sheet);
                    //县级行政单元
                    CreateCell(cellStyle, row, columnIndex++, item.getCOUNTY(), sheet);
                    //管控单元分类
                    CreateCell(cellStyle, row, columnIndex++, item.getGKDYFL(), sheet);
                    //备注
                    CreateCell(cellStyle, row, columnIndex++, item.getRemarks(), sheet);
                    //面积
                    CreateCell(cellStyle, row, columnIndex++, item.getShapeArea(), sheet);
                    //周长
                    CreateCell(cellStyle, row, columnIndex++, item.getShapeLeng(), sheet);

                    serialNum++;
                    rowStartIndexTwo++;
                }
            }
        } else {
            if (environmentAttributeList != null && environmentAttributeList.size() > 0) {
                int serialNum = 1;
                for (EnvironmentAttribute item : environmentAttributeList) {
                    int columnIndex = 0;
                    Row row = sheet.createRow(rowStartIndexTwo);
                    row.setHeightInPoints(24);
                    //序号
                    CreateCell(cellStyle, row, columnIndex++, String.valueOf(serialNum), sheet);
                    //环境要素管控分区编码
                    CreateCell(cellStyle, row, columnIndex++, item.getHJDYBM(), sheet);
                    //环境要素管控分区名称
                    CreateCell(cellStyle, row, columnIndex++, item.getHJDYMC(), sheet);
                    //省级行政单元
                    CreateCell(cellStyle, row, columnIndex++, item.getPROV(), sheet);
                    //市级行政单元
                    CreateCell(cellStyle, row, columnIndex++, item.getCITY(), sheet);
                    //县级行政单元
                    CreateCell(cellStyle, row, columnIndex++, item.getCOUNTY(), sheet);
                    //管控单元分类
                    CreateCell(cellStyle, row, columnIndex++, item.getGKDYFL(), sheet);
                    //环境要素
                    CreateCell(cellStyle, row, columnIndex++, item.getHJYS(), sheet);
                    //要素细类
                    CreateCell(cellStyle, row, columnIndex++, item.getYSXL(), sheet);
                    //流域名称
                    CreateCell(cellStyle, row, columnIndex++, item.getLYMC(), sheet);
                    //河段名称
                    CreateCell(cellStyle, row, columnIndex++, item.getHDMC(), sheet);
                    //控制断面起点经度
                    CreateCell(cellStyle, row, columnIndex++, item.getKZDMQDJD(), sheet);
                    //控制断面起点纬度
                    CreateCell(cellStyle, row, columnIndex++, item.getKZDMQDWD(), sheet);
                    //控制断面终点经度
                    CreateCell(cellStyle, row, columnIndex++, item.getKZDMZDJD(), sheet);
                    //控制断面终点纬度
                    CreateCell(cellStyle, row, columnIndex++, item.getKZDMZDWD(), sheet);
                    //面积
                    CreateCell(cellStyle, row, columnIndex++, item.getShapeArea(), sheet);
                    //周长
                    CreateCell(cellStyle, row, columnIndex++, item.getShapeLeng(), sheet);

                    serialNum++;
                    rowStartIndexTwo++;
                }
            }
        }
        //本地测试导出测试
        File file = new File(srcFinalPath);
        if (!file.exists()) {
            file.createNewFile();
            System.out.println(file.getPath());
        }
        OutputStream outputStream = new FileOutputStream(file);
        workbook.write(outputStream);
        outputStream.close();
        inputStream.close();

        return srcFinalPathResult;
    }

    private void RegionStyle(CellRangeAddress cellRangeAddress, XSSFSheet sheet) {
        RegionUtil.setBorderBottom(BorderStyle.NONE, cellRangeAddress, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.NONE, cellRangeAddress, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.NONE, cellRangeAddress, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.NONE, cellRangeAddress, sheet); // 上边框
    }

    private void CreateCell(CellStyle cellStyle, Row row, int cellIndex) {

        Cell cell = row.createCell(cellIndex);
        cell.setCellStyle(cellStyle);
    }

    private void CreateCell(CellStyle cellStyle, Row row, int cellIndex, String cellValue, XSSFSheet sheet) {
        Cell cell = row.createCell(cellIndex);
        CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum(), row.getRowNum(), cellIndex,
                cellIndex);
        RegionUtil.setBorderBottom(BorderStyle.NONE, cellRangeAddress, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.NONE, cellRangeAddress, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.NONE, cellRangeAddress, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.NONE, cellRangeAddress, sheet); // 上边框
        cell.setCellStyle(cellStyle);
        cell.setCellValue(cellValue);
    }

    private void CreateCell(CellStyle cellStyle, Row row, int cellIndex, String cellValue) {

        Cell cell = row.createCell(cellIndex);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(cellValue);
    }

    private void SetCellValue(CellStyle cellStyle, Cell cell, String value) {
        cell.setCellValue(value);
        cell.setCellStyle(cellStyle);
    }

    private void SetRegionCellValue(Map<Integer, Object> map, XSSFSheet sheet) {
        Set<Integer> indexSet = map.keySet();
        for (Integer indexRow : indexSet) {
            Map<Integer, String> cellInfo = (Map<Integer, String>) map.get(indexRow);
            Set<Integer> cellIndex = cellInfo.keySet();
            for (Integer cellIndexItem : cellIndex) {
                sheet.getRow(indexRow).getCell(cellIndexItem).setCellValue(cellInfo.get(cellIndexItem));
            }
        }

    }

    private String dateToString(Date date) {
        if (date != null) {
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");//日期格式
            String result = format.format(date);
            return result;
        }
        return "";
    }

    private Date getYearFirst(Date date) throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar = new GregorianCalendar();
        calendar.setTime(date);
        calendar.clear(Calendar.MONTH);
        calendar.add(Calendar.YEAR, 0);
        /*c.add(Calendar.MONTH, 0);*/
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        //设置为1号,当前日期既为本月第一天
        //c.set(Calendar.DAY_OF_MONTH, 1);

        String first = format.format(calendar.getTime());
        return format.parse(first);
    }

    public static Date getYearLast(Date date) throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar = new GregorianCalendar();
        calendar.setTime(date);
        calendar.clear(Calendar.MONTH);
        calendar.set(Calendar.MONTH, 11);
        calendar.set(Calendar.DAY_OF_MONTH,
                calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
        String last = format.format(calendar.getTime());

        return format.parse(last);
    }

    public static Row insertRow(XSSFSheet sheet, int startRow) {
        Row row = null;
        if (sheet.getRow(startRow) != null) {
            int lastRowNo = sheet.getLastRowNum();
            sheet.shiftRows(startRow, lastRowNo, 1, true, false);
        }
        row = sheet.createRow(startRow);
        row.setHeightInPoints(24);
        return row;
    }
}
